<?php
/**
 * DefaultController is for the default handler.
 * 
 * LBB Solutions Ltd. 版权所有 @ 2013.
 */
class DefaultController extends MobileController
{
	public function actionIndex() {
		$this->render('index');
	}
	
	public function actionWindows() {
		$this->render('windows');
	}
	
	public function actionView($id)
	{
		$params = array(
			':id' => $id,
		);
		//if loged in 
		$s_status = ', NULL as redeemed_status, NULL as redeemed_time ';
		if (!Yii::app()->user->isGuest) {
			$s_status = ', (SELECT r3.redeemed FROM tbl_merchant_special_redeem r3
					WHERE r3.id_special=s.id AND r3.id_user=:id_user AND DATE(r3.insert_timestamp)=DATE(NOW())
					ORDER BY r3.insert_timestamp DESC LIMIT 1) AS redeemed_status
					, (SELECT unix_timestamp(r4.redeemed_time) FROM tbl_merchant_special_redeem r4
					 WHERE r4.id_special=s.id AND r4.id_user=:id_user AND DATE(r4.insert_timestamp)=DATE(NOW())
					 ORDER BY r4.insert_timestamp DESC LIMIT 1) AS redeemed_time ';// add redeemed status only for today
			$params[':id_user'] = Yii::app()->user->id;
		}
		
		$sql_offer = 'SELECT s.id, s.id_merchant, s.url_picture, m.url_logo, s.content,
			s.price, s.value, s.discount, s.show_hours, s.start_hour, s.end_hour, s.week_days, s.status, s.type, s.max_number, s.redeem_type,
			day_1_start_hour, day_2_start_hour, day_3_start_hour, day_4_start_hour, day_5_start_hour, day_6_start_hour, day_7_start_hour,
			day_1_end_hour, day_2_end_hour, day_3_end_hour, day_4_end_hour, day_5_end_hour, day_6_end_hour, day_7_end_hour,
			UNIX_TIMESTAMP(s.published_time) AS uts_published_time, UNIX_TIMESTAMP(s.start_date) AS uts_start_date, UNIX_TIMESTAMP(s.end_date) AS uts_end_date,
			(SELECT SUM(r1.number) FROM tbl_merchant_special_redeem r1 WHERE r1.id_special=s.id AND DATE(r1.insert_timestamp)=DATE(NOW()) ) AS claimed_num,
			m.name, m.category_key, m.url_logo, m.url_offer_img, m.phone, m.address, m.biz_area, m.postcode, m.city, m.latitude, m.longitude '
			. $s_status . '
			FROM tbl_merchant_special s
			INNER JOIN tbl_merchant_account m ON m.id=s.id_merchant
			WHERE s.trash=0 AND s.status=1 AND s.id=:id ';
		$connection = Yii::app()->db;
		$command = $connection->createCommand($sql_offer);
		$row = $command->queryRow(true, $params);
		
		if (empty($row)) {
			$connection->active = false;
			throw new CHttpException(404, 'The requested page does not exist.');
		}
		
		//get offer thumbs
		$row['thumbs'] = array();
		
		//+1 viewed
		$userId = Yii::app()->user->isGuest ? null : Yii::app()->user->id;
		$ipAddress = Yii::app()->request->getUserHostAddress();
		$sql_insert = 'INSERT INTO tbl_merchant_special_viewed (id_user, id_special, ip, agent, action) VALUES (:id_user, :id_special, :ip, :agent, :action)';
		$command = $connection->createCommand($sql_insert);
		$command->execute(array(
			':id_user'=>$userId,
			':id_special'=>$id,
			':ip'=>$ipAddress,
			':agent'=>Yii::app()->request->getUserAgent(),
			':action'=>3,//3 - mobile web view
		));
		$connection->active = false;
		
		$this->render('view', array('r' => $row));
	}
	
	public function actionOffers() {
		//fetch offers nearby
		// set the limit number and offset
		$offset = Yii::app()->request->getParam('offset', 0);
		$limit = Yii::app()->request->getParam('limit', 25);
		if ($limit > 25) $limit = 25;
		$s_limit = " LIMIT {$offset}, " . $limit;
		
		$params = array();
		// get distance if lat=? and lng=?
		$enable_geo = false;
		$s_distance = 'NULL AS distance, ';
		$latitude = Yii::app()->request->getParam('lat');
		$longitude= Yii::app()->request->getParam('lng');
		if (!empty($latitude) and !empty($longitude)) {
			$enable_geo = true;
			$s_distance = '(6371 * acos(cos(radians(:lat)) * cos(radians(m.latitude)) * cos(radians(m.longitude) - radians(:lng)) + sin(radians(:lat)) * sin(radians(m.latitude)))) AS distance, ';
			$params[':lat'] = (double)$latitude;
			$params[':lng'] = (double)$longitude;
		}
		
		// fetch specials
		$params[':week_days'] = '%'.date('N').'%';	//week_days => 5
		$sql_specials = 'SELECT s.id, s.id_merchant, s.url_picture, s.content, s.show_hours, s.start_hour, s.end_hour, s.week_days,
			s.price, s.value, s.discount, s.type, ' . $s_distance . '
			UNIX_TIMESTAMP(s.start_date) AS uts_start_date, UNIX_TIMESTAMP(s.end_date) AS uts_end_date,
			s.max_number,
			m.name, m.address, m.biz_area, m.postcode, m.city, m.phone, m.url_logo, m.url_offer_img, m.currency_code, m.category_key, m.latitude, m.longitude, '
			. ' (SELECT SUM(r1.number) FROM tbl_merchant_special_redeem r1
				 WHERE r1.id_special=s.id AND DATE(r1.insert_timestamp)=DATE(NOW()) ) AS claimed_num, '	// calculate today's claimed_num
			. ' (SELECT SUM(r2.number) FROM tbl_merchant_special_redeem r2
				 WHERE r2.id_special=s.id AND r2.redeemed=2 AND DATE(r2.redeemed_time)=DATE(NOW()) ) AS redeemed_num '	// calculate today's redeemed_num
			. ' FROM tbl_merchant_special s
			INNER JOIN tbl_merchant_account m ON m.id=s.id_merchant
			WHERE s.status=1 AND s.trash=0 AND m.status=1 AND s.end_date>NOW()
			  AND (s.week_days LIKE :week_days)
			GROUP BY s.id
			ORDER BY distance ASC, s.published_time DESC '
			. $s_limit;
		$connection = Yii::app()->db;
		$command = $connection->createCommand($sql_specials);
		$rows = $command->queryAll(true, $params);
		$connection->active = false;
		
		$this->renderPartial('_list', array('rows' => $rows));
	}
	
	public function actionMerchants() {
		
	}
}